﻿using System;
using DMS.Base;
using System.Data.Common;
using System.Data;
using System.Data.SqlClient;
using DMS.Business.Modules.Organization;
using DMS.Business.Utility;

namespace DMS.Business.Modules.SystemManagement
{
    public class NoticeService
    {
        public static int CountNotice(string condition)
        {
            string countSql = String.Format(@"select count(*) num
	                                            from Sys_notice n 
                                            where n.Deleted = 0                                            
                                            {0}", condition);
            DbCommand command = DBHelper.Instance.GetSqlStringCommand(countSql);
            DataSet ds = DBHelper.Instance.ExecuteDataSet(command);
            return Convert.ToInt32(ds.Tables[0].Rows[0]["num"]);
        }

        public static System.Data.DataSet ListNotice(int pageSize, int pageIndex, int total, string condition)
        {
            string sql = String.Format(@"select *
	                                        from Sys_notice n 
                                        where n.Deleted = 0
                                        {0} order by publishTime desc", condition);
            SqlCommand command = new SqlCommand(sql, new SqlConnection(DBHelper.Instance.ConnectionString));
            SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
            DataSet ds = new DataSet();
            dataAdapter.Fill(ds, pageIndex - 1, pageSize, "Table1");
            return ds;
        }

        public static DataSet ListMyNotice(int pageSize, int pageIndex, int total, string condition)
        {
            User user = UserHelper.GetContextUser();
            string sql = String.Format(@"select *
	                                from Sys_notice n 
                                where (n.RoleID is null or 
		                                exists (select RoleID from ORG_RoleMembers rm where rm.MemberID = @UserID and rm.RoleID = n.RoleID))                                
                                and n.Deleted = 0 {0}", condition);
            SqlCommand command = new SqlCommand(sql, new SqlConnection(DBHelper.Instance.ConnectionString));
            SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
            command.Parameters.Add(DBHelper.CreateParamer("UserID", user.ID));
            DataSet ds = new DataSet();
            dataAdapter.Fill(ds, pageIndex - 1, pageSize, "Table1");
            return ds;
        }

        public static System.Data.DataSet ListMyNotice(string condition)
        {
            User user = UserHelper.GetContextUser();
            string sql = String.Format(@"select *
	                                from Sys_notice n 
                                where (n.RoleID is null or 
		                                exists (select RoleID from ORG_RoleMembers rm where rm.MemberID = @UserID and rm.RoleID = n.RoleID))
                                and not exists (select ID from Sys_NoticeKnown k where k.UserID = @UserID and k.NoticeID = n.ID)
                                and n.Deleted = 0 {0}", condition);
            DbCommand command = DBHelper.Instance.GetSqlStringCommand(sql);
            command.Parameters.Add(DBHelper.CreateParamer("UserID", user.ID));
            DataSet ds = DBHelper.Instance.ExecuteDataSet(command);
            return ds;
        }

        public static int CountMyNotice()
        {
            User user = UserHelper.GetContextUser();
            string countSql = @"select count(*) num
	                                from Sys_notice n 
                                where (n.RoleID is null or 
		                                exists (select RoleID from ORG_RoleMembers rm where rm.MemberID = @UserID and rm.RoleID = n.RoleID))
                                and not exists (select ID from Sys_NoticeKnown k where k.UserID = @UserID and k.NoticeID = n.ID)
                                and n.Deleted = 0";
            DbCommand command = DBHelper.Instance.GetSqlStringCommand(countSql);
            command.Parameters.Add(DBHelper.CreateParamer("UserID", user.ID));
            DataSet ds = DBHelper.Instance.ExecuteDataSet(command);
            return Convert.ToInt32(ds.Tables[0].Rows[0]["num"]);
        }

        public static int CountMyNotice(string condition)
        {
            User user = UserHelper.GetContextUser();
            string countSql = String.Format(@"select count(*) num
	                                from Sys_notice n 
                                where (n.RoleID is null or 
		                                exists (select RoleID from ORG_RoleMembers rm where rm.MemberID = @UserID and rm.RoleID = n.RoleID))                                
                                and n.Deleted = 0 {0}", condition);
            DbCommand command = DBHelper.Instance.GetSqlStringCommand(countSql);
            command.Parameters.Add(DBHelper.CreateParamer("UserID", user.ID));
            DataSet ds = DBHelper.Instance.ExecuteDataSet(command);
            return Convert.ToInt32(ds.Tables[0].Rows[0]["num"]);
        }



        public static void DeleteNotice(string id)
        {
            string sql = @"update Sys_notice set deleted = 1 where ID = @ID";
            DbCommand command = DBHelper.Instance.GetSqlStringCommand(sql);
            command.Parameters.Add(DBHelper.CreateParamer("ID", id));
            DBHelper.Instance.ExecuteNonQuery(command);            
        }

        public static Sys_notice GetNotice(string id)
        {
            string sql = @"select * from sys_notice where id = @ID";
            DbCommand command = DBHelper.Instance.GetSqlStringCommand(sql);
            command.Parameters.Add(DBHelper.CreateParamer("ID", id));
            DataSet ds = DBHelper.Instance.ExecuteDataSet(command);
            DataRow dr = ds.Tables[0].Rows[0];
            Sys_notice notice = FillContent(dr);
            return notice;
        }

        public static void UpdateNotice(Sys_notice notice)
        {
            string sql = @"UPDATE [Sys_notice]
                               SET [RoleID] = @RoleID
                                  ,[Content] = @Content
                                  ,[Title] = @Title
                                  ,[Publisher] = @Publisher
                                  ,[PublisherID] = @PublisherID
                                  ,[PublishTime] = @PublishTime
                             WHERE [ID] = @ID";
            DbCommand command = DBHelper.Instance.GetSqlStringCommand(sql);
            command.Parameters.Add(DBHelper.CreateParamer("RoleID", DBHelper.getValue(notice.RoleID)));
            command.Parameters.Add(DBHelper.CreateParamer("Content", DBHelper.getValue(notice.Content)));
            command.Parameters.Add(DBHelper.CreateParamer("Title", DBHelper.getValue(notice.Title)));
            command.Parameters.Add(DBHelper.CreateParamer("Publisher", DBHelper.getValue(notice.Publisher)));
            command.Parameters.Add(DBHelper.CreateParamer("PublisherID", DBHelper.getValue(notice.PublisherID)));
            command.Parameters.Add(DBHelper.CreateParamer("PublishTime", DBHelper.getValue(notice.PublishTime)));
            command.Parameters.Add(DBHelper.CreateParamer("ID", DBHelper.getGuidValue(notice.ID)));
            DBHelper.Instance.ExecuteNonQuery(command);
        }

        public static void SaveNotice(Sys_notice notice)
        {
            string sql = @"INSERT INTO [Sys_Notice]
                                   ([ID]
                                   ,[RoleID]
                                   ,[Content]
                                   ,[Title]
                                   ,[Publisher]
                                   ,[PublisherID]
                                   ,[PublishTime]
                                   ,[Deleted])
                             VALUES
                                   (NEWID()
                                   ,@RoleID
                                   ,@Content
                                   ,@Title
                                   ,@Publisher
                                   ,@PublisherID
                                   ,@PublishTime
                                   ,0)";
            DbCommand command = DBHelper.Instance.GetSqlStringCommand(sql);
            command.Parameters.Add(DBHelper.CreateParamer("RoleID", DBHelper.getValue(notice.RoleID)));
            command.Parameters.Add(DBHelper.CreateParamer("Content", DBHelper.getValue(notice.Content)));
            command.Parameters.Add(DBHelper.CreateParamer("Title", notice.Title));
            command.Parameters.Add(DBHelper.CreateParamer("Publisher", notice.Publisher));
            command.Parameters.Add(DBHelper.CreateParamer("PublisherID", notice.PublisherID));
            command.Parameters.Add(DBHelper.CreateParamer("PublishTime", notice.PublishTime));
            DBHelper.Instance.ExecuteNonQuery(command);
        }

        public static Sys_notice FillContent(DataRow dr)
        {
            Sys_notice notice = new Sys_notice();
            notice.ID = new Guid(Convert.ToString(dr["ID"]));
            if (!dr.IsNull("RoleID"))
            {
                notice.RoleID = Convert.ToString(dr["RoleID"]);
            }
            if (!dr.IsNull("Content"))
            {
                notice.Content = Convert.ToString(dr["Content"]);
            }
            if (!dr.IsNull("Title"))
            {
                notice.Title = Convert.ToString(dr["Title"]);
            }

            if (!dr.IsNull("Publisher"))
            {
                notice.Publisher = Convert.ToString(dr["Publisher"]);
            }
            if (!dr.IsNull("PublisherID"))
            {
                notice.PublisherID = Convert.ToString(dr["PublisherID"]);
            }
            if (!dr.IsNull("PublishTime"))
            {
                notice.PublishTime = Convert.ToDateTime(dr["PublishTime"]);
            }
            if (!dr.IsNull("Deleted"))
            {
                notice.Deleted = Convert.ToBoolean(dr["Deleted"]);
            }
            return notice;
        }

        public static DataSet GetALLRoles()
        {
            string sql = @"select * from org_roles";
            DbCommand command = DBHelper.Instance.GetSqlStringCommand(sql);            
            DataSet ds = DBHelper.Instance.ExecuteDataSet(command);
            
            return ds;
        }

        public static void IgnoreMyNotice()
        {
            string sql = @"insert into Sys_NoticeKnown(ID, NoticeID, UserID, ReadTime) select NEWID(), n.ID, @UserID, GETDATE()
	                                from Sys_notice n 
                                where (n.RoleID is null or 
		                                exists (select RoleID from ORG_RoleMembers rm where rm.MemberID = @UserID and rm.RoleID = n.RoleID))
                                and not exists (select ID from Sys_NoticeKnown k where k.UserID = @UserID and k.NoticeID = n.ID)
                                and n.Deleted = 0";
            User user = UserHelper.GetContextUser();
            DbCommand command = DBHelper.Instance.GetSqlStringCommand(sql);
            command.Parameters.Add(DBHelper.CreateParamer("UserID", user.ID));
            DBHelper.Instance.ExecuteNonQuery(command);
        }

        public static DataSet ListAllNotice()
        {
            User user = UserHelper.GetContextUser();
            string sql = @"select *
	                                from Sys_notice n 
                                where (n.RoleID is null or 
		                                exists (select RoleID from ORG_RoleMembers rm where rm.MemberID = @UserID and rm.RoleID = n.RoleID))                                
                                and n.Deleted = 0";
            DbCommand command = DBHelper.Instance.GetSqlStringCommand(sql);
            command.Parameters.Add(DBHelper.CreateParamer("UserID", user.ID));
            DataSet ds = DBHelper.Instance.ExecuteDataSet(command);
            return ds;
        }
    }
}
